<%-- 
    Document   : trade.physical.contract.ajax-source
    Created on : Dec 13, 2012, 4:58:22 PM
    Author     : kiendn
--%>

<%@page import="java.text.ParseException"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.text.DateFormat"%>
<%@page import="pc.sql.value.FloatValue"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="pc.sql.value.DateValue"%>
<%@page import="pc.sql.value.IntValue"%>
<%@page import="pc.sql.value.BooleanValue"%>
<%@page import="pc.sql.value.StringValue"%>
<%@page import="java.util.Vector"%>
<%@page import="pc.util.Utils"%>
<%@page import="wstrader.util.Common"%>
<%@page import="java.util.Date"%>
<%@page import="java.util.Calendar"%>
<%@page import="wstrader.Trader"%>
<%@page import="wstrader.Users"%>
<%@page import="wstrader.task.trade.physical.Contract"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <%
            float sal_ton = 0;
            float pur_ton = 0;
            float sal_delvd = 0;
            float sal_pend = 0;
            float pur_delvd = 0;
            float pur_pend = 0;


            int dLoop = 0;
            int R_id = 0;
            String Colour = "";
            float pending = 0;
            float unfixed = 0;

            ResultSet rs = null;
            Users usr = Trader.getUser(request.getParameter("uid"));
            Contract task = usr.trade.physical.contract;

            String CntType = request.getParameter("cnt_type");
            if (CntType.compareTo("") == 0) {
                CntType = "0";
            }
            int GradeId = Integer.parseInt(request.getParameter("grade"));
            int BuySellId = Integer.parseInt(request.getParameter("buyer_seller"));

            DateFormat DOB = new SimpleDateFormat("yyyy-MM-dd");
            java.sql.Date convertedDate = null;
            try {
                convertedDate = new java.sql.Date(DOB.parse(request.getParameter("last_date")).getTime());
            } catch (ParseException ex) {
                ex.printStackTrace();
            }
            java.sql.Date LsDt = convertedDate;

            Calendar ca = Calendar.getInstance();
            ca.setTime(LsDt);
            ca.add(Calendar.MONTH, -1);
            Date previous_month = ca.getTime();
            java.sql.Date sql_pre_month = new java.sql.Date(previous_month.getTime());

            String date_query = "(last_date < '" + LsDt + "' OR " + (request.getParameter("last_date").compareTo("1970-01-01") == 0) + ") ";

            if (LsDt.getTime() > 0) {
                Date new_last_date = new Date(LsDt.getTime());
                int month = Common.getMonth((Utils.dateToStr(new_last_date)).substring(3, 6));
                float month_year = Float.parseFloat((Utils.dateToStr(new_last_date)).substring(7, 9) + "." + month);
                int cur_month = Common.getMonth((Utils.dateToStr(new Date())).substring(3, 6));
                cur_month = (cur_month == 12) ? 1 : cur_month + 1;
                float cur_year_month = Float.parseFloat((Utils.dateToStr(new Date())).substring(7, 9) + "." + cur_month) + 1;
                if (month_year == cur_year_month) {
                    date_query = "(last_date < '" + LsDt + "' OR " + (request.getParameter("last_date").compareTo("1970-01-01") == 0) + ") ";
                } else {
                    date_query = "((last_date < '" + LsDt + "' AND last_date >= '" + sql_pre_month + "') OR " + (request.getParameter("last_date").compareTo("1970-01-01") == 0) + ") ";
                }
            }
            //if ()
            //Utils.strToDate(getRequest().getStringValue("ter_mth"));
            String Status = request.getParameter("status");
            int QualityId = Integer.parseInt(request.getParameter("quality"));
            if (Status.compareTo("") == 0) {
                Status = "0";
            }
            String Sql = "";
            if (Status.compareTo("0") == 0) {
                Sql = "Select * from contracts where "
                        + "(type = '" + CntType + "' OR " + (CntType.compareTo("0") == 0) + ") "
                        //+ "and (origin_id = ? OR ?) "
                        + "and (grade_id = " + GradeId + " OR " + (GradeId == 0) + ") "
                        + "and (supplier_buyer_id = " + BuySellId + " OR " + (BuySellId == 0) + ") "
                        + "and (quality_id = " + QualityId + " OR " + (QualityId == 0) + ") "
                        //+ "and (last_date < ? OR ?) "
                        + "and " + date_query
                        + "and active = 1 order by id desc";

                rs = task.getSQLCommand().executeQuery(Sql);
            } else {
                if (Status.compareTo("F") == 0) {
                    Sql = "Select * from contracts where "
                            + "(type = '" + CntType + "' OR " + (CntType.compareTo("0") == 0) + ") "
                            //+ "and (origin_id = ? OR ?) "
                            + "and (grade_id = '" + GradeId + "' OR " + (GradeId == 0) + ") "
                            + "and (supplier_buyer_id = '" + BuySellId + "' OR " + (BuySellId == 0) + ") "
                            + "and (quality_id = '" + QualityId + "' OR " + (QualityId == 0) + ") "
                            //+ "and ((last_date < ? OR ?) OR (last_date IS NULL)) "
                            + "and (" + date_query + " OR (last_date IS NULL)) "
                            + "and (unfixed_tons=0) order by id desc";
                }
                if (Status.compareTo("U") == 0) {
                    Sql = "Select * from contracts where "
                            + "(type = '" + CntType + "' OR " + (CntType.compareTo("0") == 0) + ") "
                            //+ "and (origin_id = ? OR ?) "
                            + "and (grade_id = '" + GradeId + "' OR " + (GradeId == 0) + ") "
                            + "and (supplier_buyer_id = '" + BuySellId + "' OR " + (BuySellId == 0) + ") "
                            + "and (quality_id = '" + QualityId + "' OR " + (QualityId == 0) + ") "
                            //+ "and ((last_date < ? OR ?) OR (last_date IS NULL)) "
                            + "and (" + date_query + " OR (last_date IS NULL)) "
                            + "and (unfixed_tons>0) order by id desc";
                }
                if (Status.compareTo("P") == 0) {
                    Sql = "Select * from contracts where "
                            + "(type = '" + CntType + "' OR " + (CntType.compareTo("0") == 0) + ") "
                            //+ "and (origin_id = ? OR ?) "
                            + "and (grade_id = '" + GradeId + "' OR " + (GradeId == 0) + ") "
                            + "and (supplier_buyer_id = '" + BuySellId + "' OR " + (BuySellId == 0) + ") "
                            + "and (quality_id = '" + QualityId + "' OR " + (QualityId == 0) + ") "
                            //+ "and ((last_date < ? OR ?) OR (last_date IS NULL)) "
                            + "and (" + date_query + " OR (last_date IS NULL)) "
                            + "and (pending_tons>0) order by id desc";
                }
                if (Status.compareTo("D") == 0) {
                    Sql = "Select * from contracts where "
                            + "(type = '" + CntType + "' OR " + (CntType.compareTo("0") == 0) + ") "
                            //+ "and (origin_id = ? OR ?) "
                            + "and (grade_id = '" + GradeId + "' OR " + (GradeId == 0) + ") "
                            + "and (supplier_buyer_id = '" + BuySellId + "' OR " + (BuySellId == 0) + ") "
                            + "and (quality_id = '" + QualityId + "' OR " + (QualityId == 0) + ") "
                            //+ "and ((last_date < ? OR ?) OR (last_date IS NULL)) "
                            + "and (" + date_query + " OR (last_date IS NULL)) "
                            + "and (pending_tons=0) order by id desc";
                }

                rs = task.getSQLCommand().executeQuery(Sql);
            }
            System.out.println(Sql);
        %>
        <link href="styles/jquery-ui-1.8.4.custom.css" rel="stylesheet" type="text/css"/>
        <link href="styles/demo_table_jui.css" rel="stylesheet" type="text/css"/>
    </head>
    <body>
        <table id="mytable" style="width: 100% !important; font-size: 10px">
            <thead>
                <tr>
                    <th class="style11">Id</th>
                    <th class="style11">Purchase Ref.No</th>
                    <th class="style11">Origin</th>
                     <th class="style11">Grade</th>
                    <th class="style11">Supplier</th>
                    <th class="style11">First Date </th>
                    <th class="style11">Last Date </th>
                    <th class="style11">Total</th>
                    <th class="style11">Delivered</th>
                    <th class="style11">Balance</th>
                    <th class="style11">FOB Differential </th>
                </tr>
            </thead>
            <tbody>
                <%
                    while (rs.next()) {
                        R_id = rs.getInt("id");
                        pending = rs.getFloat("Tons") - rs.getFloat("receive_tons");
                        unfixed = rs.getFloat("unfixed_tons");
                        if ((pending > 0) && (unfixed > 0)) {
                            Colour = "#FF0000";
                        }
                        if ((pending == 0) && (unfixed > 0)) {
                            Colour = "#FFFF66";
                        }
                        if ((pending > 0) && (unfixed == 0)) {
                            Colour = "#33CCFF";
                        }
                        if ((pending == 0) && (unfixed == 0)) {
                            Colour = "#000011";
                        }
                %>
                <tr id="<%= R_id%>"  onDblClick="cardViewClick(this);"  onClick="highlightOn(this);">
                    <td width="2%" height="20" align="center" class="style11" ><%=rs.getInt("id")%></td>
                    <td width="8%" align="center" class="style11" style="color:<%=Colour%>"><%=rs.getString("ref_number")%></td>
                    <td width="8%" class="style11" style="color:<%=Colour%>" align="center"><%=rs.getString("contract_no")%></td>		  
                    <td width="15%" class="style11" align="left" ><%=task.getFieldValueForId("grade_master", "name", rs.getInt("grade_id"))%></td>
                    <td width="12%" class="style11" align="left" ><%=task.getFieldValueForId("company_master", "name", rs.getInt("supplier_buyer_id"))%></td>
                    <td width="8%" class="style11" align="center" ><%=Utils.dateToStr(rs.getDate("first_date"))%></td>
                    <td width="8%" class="style11" align="center" ><%=Utils.dateToStr(rs.getDate("last_date"))%></td>
                    <td width="6%" class="style11" align="right" ><%=Utils.numberToStr(rs.getFloat("tons"), 3)%></td>
                    <%
                        if (rs.getString("type").compareTo("P") == 0) {
                            pur_ton = pur_ton + rs.getFloat("tons");
                            pur_delvd = pur_delvd + rs.getFloat("receive_tons");
                            pur_pend = pur_pend + (rs.getFloat("Tons") - rs.getFloat("receive_tons"));
                        }
                        if (rs.getString("type").compareTo("S") == 0) {
                            sal_ton = sal_ton + rs.getFloat("tons");
                            sal_delvd = sal_delvd + rs.getFloat("receive_tons");
                            sal_pend = sal_pend + (rs.getFloat("Tons") - rs.getFloat("receive_tons"));
                        }

                    %>
                    <td width="6%" class="style11" align="right" ><%=Utils.numberToStr(rs.getFloat("receive_tons"), 3)%></td>		  
                    <td width="5%" class="style11" align="right" ><%=Utils.numberToStr(rs.getFloat("Tons") - rs.getFloat("receive_tons"), 3)%></td>
                    <td width="6%" class="style11" align="right"><%=Utils.numberToStr(rs.getFloat("differential_fob"), 2)%></td>
                </tr>
                <%
                        dLoop = dLoop + 1;
                    }
                %>
            </tbody>
            <tfoot class="style11">
                <tr>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th class="style11"><strong>Total Purchase</strong></th>
                    <th></th>
                    <th></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(pur_ton, 3)%></strong></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(pur_delvd, 3)%></strong></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(pur_pend, 3)%></strong></th>
                    <th></th>
                </tr>
                <tr>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th class="style11"><strong>Total Sales </strong></th>
                    <th></th>
                    <th></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(sal_ton, 3)%></strong></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(sal_delvd, 3)%></strong></th>
                    <th class="style11" align="right"><strong><%=Utils.numberToStr(sal_pend, 3)%></strong></th>
                    <th></th>
                </tr>
            </tfoot>
        </table>
        <script type="text/javascript">
            $(document).ready(function(){
                oTable = $("#mytable").dataTable({
                    "sScrollY": 250,
                    "bJQueryUI": true,
                    "aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
                    "sPaginationType": "full_numbers"
                });
            });
        </script>
    </body>
</html>
